package com.bw.dao;

import com.bw.doman.sugar;
import com.bw.doman.sugarAge;
import com.bw.doman.sugarFen;
import com.bw.doman.sugarTable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface sugarDao {
    @Select("SELECT b.name,sum(o.final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN base_province b on o.province_id=b.id\n" +
            "GROUP BY b.name")
    List<sugar> getList();


    @Select("SELECT t.tm_name name,sum(o.final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark t on s.tm_id=t.tm_id\n" +
            "GROUP BY t.tm_name")
    List<sugar> getList2();


    @Select("SELECT c1.name,sum(o.final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 on s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 on c3.category2_id=c2.id\n" +
            "LEFT JOIN  base_category1 c1 on c2.category1_id=c1.id\n" +
            "GROUP BY c1.name")
    List<sugar> getList3();

    @Select("SELECT * FROM\n" +
            "(SELECT count(*) newUse FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW()) >=12)a,\n" +
            "(SELECT count(*) oldUse FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW()) <12)b")
    List<sugarTable> getList4();

    @Select("SELECT c2.name,sum(final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d on d.order_id=o.id \n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id \n" +
            "LEFT JOIN base_category3 c3 on s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 on c3.category2_id=c2.id\n" +
            "GROUP BY c2.name")
    List<sugar> getList5();

    @Select("SELECT concat(SUBSTRING(birthday,3,1),'0后') `name`,sum(final_total_amount) value FROM user_info u \n" +
            "LEFT JOIN order_info o on o.user_id=u.id\n" +
            "GROUP BY  concat(SUBSTRING(birthday,3,1),'0后')")
    List<sugar> getList6();

    @Select("SELECT yys name,sum(final_total_amount) value FROM order_info WHERE yys is not null GROUP BY yys\n")
    List<sugar> getList7();


    @Select("SELECT t.tm_name name,\n" +
            "if(u.gender='F','男','女') age,\n" +
            "sum(o.final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN user_info u on o.user_id=u.id\n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark t on s.tm_id=t.tm_id\n" +
            "GROUP BY t.tm_name,u.gender")
    List<sugarAge> getList8();

    @Select("SELECT sum(final_total_amount) value FROM order_info \n")
    double getList9();

    @Select("SELECT p.spu_name name,sum(o.final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id \n" +
            "LEFT JOIN spu_info p on s.spu_id=p.id\n" +
            "GROUP BY p.spu_name")
    List<sugarFen> getList10();
}
